#!/usr/bin/perl

use Cwd;

$baseurl = getcwd;

if ($ARGV[0] eq 'CREATING') {
    $mode = 'CREATING';
} else {
    $mode = 'UPDATING';
}

if ($ARGV[1] =~ /\//) {
    ($baseurl = $ARGV[1]) =~ s/[\/]*$//;
}

$error_outs = $baseurl . '/items_sequences_errors.txt';
$print_outs = $baseurl . '/items_sequences_prints.txt';

binmode(STDOUT, ':utf8');

open (STDERR, ">$error_outs");
open (STDOUT, ">$print_outs");

print "baseurl: " . $baseurl . "\n";

# local system variables
$connect = $baseurl . '/connect.pl';
require $connect;

use IO::File;

#use Data::Dumper;

use URI::Escape;

use LWP::Simple;  # used to fetch the chatterbox ticker

use XML::Parser;

use utf8;

use Unicode::Normalize;

%db_local_tables_hash = ("bibs_record_table" => $institution . '_bibs', "bibs_record_temp_table" => $institution . '_bibs_temp', "items_record_table" => $institution . '_items', "items_record_temp_table" => $institution . '_items_temp', "items_sequences_table" => $institution . '_items_sequences', "holdings_record_table" => $institution . '_holdings', "checkins_record_table" => $institution . '_checkins', "universal_ids_table" => $institution . '_universal_ids');

%db_consortium_tables_hash = ("bibs_record_table" => $consortium . '_bibs', "items_record_table" => $consortium . '_items', "holdings_record_table" => $consortium . '_holdings', "checkins_record_table" => $consortium . '_checkins');

use DBI;

$filecount = 0;
$previous_time = '';

# connection for the select
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass) or die "couldn't connect to database: " . DBI->errstr;

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_time = "$HOUR:$MIN:$SEC";

if ($ARGV[0] eq 'CREATING') {
    $this_table = $db_local_tables_hash{'items_record_table'};
} elsif ($ARGV[0] eq 'UPDATING') {
    $this_table = $db_local_tables_hash{'items_record_temp_table'}
}

        $querystring = "SELECT COUNT( * ) AS n, local_control_number
            FROM $this_table
            WHERE local_control_number != ''
            AND item_sequence = 0
            GROUP BY local_control_number
            HAVING n > 1";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);


($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$this_time = "$HOUR:$MIN:$SEC";

#print "query: " . $querystring . " done\n" . $start_time . "\n" . $this_time . "\n\n";

        while (@row = $query->fetchrow_array) {

#print @row[1] . "\n";

            $items_count = @row[0];
            $institution = substr(@row[1], 0, 5);
            $bib_record = substr(@row[1], 5);

    $int = $filecount / 10000;

    if ($int =~ m/^\d+$/) {

        if($filecount > 0) {
            $batch_loader->autoflush(1);
            close($batch_loader, 2) || "cannot close $batch_loader: $!";
        }

        $batch_textfiles = $baseurl . '/items_item_sequences_textfiles/item_sequences_' . $filecount . ".txt";

        $batch_loader = IO::File->new("> $batch_textfiles") || die "cannot open $batch_textfiles for writing: $!\n";
        binmode( $batch_loader, ':utf8' );
        flock($batch_loader, 2);
    }

$filecount++;

%query_insert = (
  item_record => '',
  item_sequence => '',
  local_control_number => ''
);

$next_file = $xmlopac_baseurl . "." . $bib_record . "/0/0/1/5?noexclude=WXROOT.Heading.Title.IIIRECORD&links=i1-" . $items_count;

#print "next_file: " . $next_file . "\n";

$xmlfile = get($next_file);

# initialize parser object and parse the string

  $parser = new XML::Parser ( Handlers => {   # Creates our parser object
                              Start   => \&hdl_start,
                              End     => \&hdl_end,
                              Char    => \&hdl_char,
                              Default => \&hdl_def,
                            });
    eval { $parser->parse($xmlfile, forcearray => 1); };

# report any error that stopped parsing, or announce success
if( $@ ) {
    $@ =~ s/at \/.*?$//s;               # remove module line number
    print "ERROR in " . $institution . $bib_record . $@ . "$next_file\n\n";
($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$this_time = "$HOUR:$MIN:$SEC";

print $this_time . "\n";

} else {

  # The Handlers
  sub hdl_start{
      ($p, $element, %atts) = @_;
        if($element eq 'RecordKey') {
            $tag = 'RecordKey';
        } elsif($element eq 'SequenceNumber') {
            $tag = 'SequenceNumber';
        }
  }
   
  sub hdl_end{
      ($p, $element) = @_;
        $tag = '';

        if($element eq 'RecordKey') {
            if($recordkey =~ /^i/) {
                $query_insert{'item_record'} = $institution . $recordkey;

            #placement for the insert into allitems_new_sequences
                @keys = keys %query_insert;
                @values = values %query_insert;

                $mysql_keys = join(", ", @keys);
                $mysql_values = join("\t", @values);
                $mysql_values =~ s/\\\t/\\ \t/g;

                print $batch_loader $mysql_values . "\n";
            }
        }

}
  
  sub hdl_char {
      ($p, $str) = @_;
        if($tag eq 'RecordKey') {
            $recordkey = $str;
            if($recordkey =~ /^b/) {
                $query_insert{'local_control_number'} = $institution . $recordkey;
            }
        } elsif($tag eq 'SequenceNumber') {
            $query_insert{'item_sequence'} = $str;
        }
  }
  
  sub hdl_def { }  # We just throw everything else away

    }
}
  
$previous_time = "$this_time";

if($filecount > 0) {
        $batch_loader->autoflush(1);
        close($batch_loader, 2) || "cannot close $batch_loader: $!";
}


($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_of_file_creation_time = "$HOUR:$MIN:$SEC";

$sth2 = $dbh->do("TRUNCATE TABLE $db_local_tables_hash{'items_sequences_table'}");

$where = $baseurl . '/items_item_sequences_textfiles';

while (defined($next_file = <$where/*.txt>)) {

    $sth3 = $dbh->do("LOAD DATA INFILE '" . $next_file . "' REPLACE INTO TABLE $db_local_tables_hash{'items_sequences_table'} FIELDS TERMINATED BY '\t' (" . $mysql_keys . ")") || print "died again";

unlink($next_file);

}

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$allitems_new_sequences_update_time = "$HOUR:$MIN:$SEC";

if ($ARGV[0] eq 'CREATING') {
    $this_table = $db_local_tables_hash{'items_record_table'};
} elsif ($ARGV[0] eq 'UPDATING') {
    $this_table = $db_local_tables_hash{'items_record_temp_table'}
}

$that_table = $db_local_tables_hash{'items_sequences_table'};

        $querystring = "UPDATE $this_table, $that_table
            SET $this_table.item_sequence = $that_table.item_sequence 
            WHERE $this_table.item_record = $that_table.item_record
            AND $this_table.local_control_number = $that_table.local_control_number"; 

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

        $query->finish();

$dbh->disconnect;

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_time = "$HOUR:$MIN:$SEC";

if($total_time > 0) {
$total_time = $total_time / 60;
$total_time =~ s/\./:/;
}

print "Start time: " . $start_time . "\nEnd of file creation time: " . $end_of_file_creation_time . "\nallitems_new_sequences update time" . $allitems_new_sequences_update_time . "\nEnd time: " . $end_time . "\n Number of files: " . $filecount . "\n Total time in minutes: " . $total_time . "\n";

close (STDERR) || "cannot close errors.txt: $!";
close (STDOUT) || "cannot close prints.txt: $!";

